﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using IQ.Base.Services;
using IQ.BaseWindows;
using IQ.DbBase;
using SqlServerAdmin;

namespace IQ.DbA
{
    partial class FrmQuery : Form
    {
        #region # Constructor #
        public FrmQuery()
        {
            InitializeComponent();
        }
        #endregion

        #region - Private variables -
        private string curWord = "";
        private int iCMD = -1;
        private SqlQuery sqlQry;
        #endregion

        #region - Private methods -
        private int findReservedWord()
        {
            int iRes = -1;
            if (curWord.Length > 0)
            {
                for (int i = 1; i < lbxCMD.Items.Count; i++)
                {
                    if ((curWord.Length <= lbxCMD.Items[i].ToString().Length)
                        &&
                        (string.Compare(curWord,
                                        lbxCMD.Items[i].ToString().Substring(0, curWord.Length), false) == 0))
                    {
                        iRes = i;
                        break;
                    }
                }
            }
            return iRes;
        }
        private void runQuery(bool asTable)
        {
            if (asTable)
                sqlQry = new SqlTable();
            else
                sqlQry = new SqlQuery();
            sqlQry.RunQuery(reQry.Text);
            grdResults.DataSource = sqlQry.ResultTable;
            stsRecordCount.Text = "Record count = " + sqlQry.ResultCount.ToString() + " ";
        }
        private void doTableChanges()
        {
            this.Cursor = Cursors.WaitCursor;
            try
            {
                SqlTable aTable = (SqlTable)sqlQry;
                aTable.SetTableNameFromSql(reQry.Text);
                int iChanges = aTable.GetChangeCount();
                if (iChanges > 0)
                {
                    if (General.MesDlg("There were " + iChanges.ToString() + " changes made to " + aTable.Name +
                               ", apply changes?", 'C') == DialogResult.Yes)
                    {
                        aTable.ApplyUpdates();
                        if (aTable.LastError != "")
                            General.MesDlg("Update error: " + aTable.LastError, 'E');
                    }
                }
            }
            finally
            {
                this.Cursor = Cursors.Default;
            }
        }
        #endregion


        //#region - Public properties / variables - 
        //#endregion

        //#region - Public methods - 
        //#endregion

        #region - Bind Methods -
        #endregion

        #region - Form Events -
        // This region is for event handler methods that are called without the user knowing about it.
        // Such as the page loading.
        private void FrmQuery_Load(object sender, EventArgs e)
        {
            if (DbHelper.CanConnect)
            {
                DataTable dtTables = DbHelper.Load_Table(SqlQueries.SqlTablesDetail);
                grdTables.DataSource = dtTables;
                dtTables.DefaultView.Sort = SqlQueries.Field_TableName;
                //
                General.SetGridViewColumnsTo(grdTables, SqlConst.viewTablesDetail);
                //
            }
            cbxOperator.SelectedIndex = 0;
            reQry.AllowDrop = true;
            reQry.DragEnter += new System.Windows.Forms.DragEventHandler(this.reQryDragEnterEvent);
            reQry.DragDrop += new System.Windows.Forms.DragEventHandler(this.reQryDragDropEvent);
            //
            lbxCMD.Items.Clear();
            foreach (string W in SqlConst.sqlReservedWord)
            {
                lbxCMD.Items.Add(W);
            }
            foreach (string W in SqlConst.sqlReservedFunc)
            {
                lbxCMD.Items.Add(W);
            }
            foreach (string W in SqlConst.sqlReservedProc)
            {
                lbxCMD.Items.Add(W);
            }
        }
        private void grdTables_CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0 && e.ColumnIndex >= 0)
            {
                DataTable table = DbHelper.Load_Table(SqlQueries.GetSqlTableFieldList(grdTables.Rows[e.RowIndex].Cells[0].FormattedValue.ToString()));
                lbxFieldList.Items.Clear();
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    lbxFieldList.Items.Add(table.Rows[i][0].ToString());
                }
            }
        }
        private void grdResults_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            Logging.Error("FrmQuery, grdResults_DataError" + "=" + e.Context.ToString());
        }
        private void txtFilter_TextChanged(object sender, EventArgs e)
        {
            int colInd = grdResults.CurrentCell.ColumnIndex;
            if (txtFilter.Text.Length > 0)
            {
                if (cbxOperator.SelectedIndex == 0)
                    sqlQry.FilterData(grdResults.CurrentCell.OwningColumn.DataPropertyName, txtFilter.Text);
                else
                {
                    try
                    {
                        sqlQry.FilterData(grdResults.CurrentCell.ColumnIndex, cbxOperator.Text, txtFilter.Text);
                    }
                    catch (Exception err)
                    {
                        General.MesDlg("Could not filter:" + err.Message, 'E');
                    }
                }
            }
            else
                sqlQry.FilterData(grdResults.CurrentCell.ColumnIndex, cbxOperator.Text, "");
            grdResults.CurrentCell = grdResults.Rows[0].Cells[colInd];
        }        
        #endregion

        #region - Control Methods -
        // This region is for event handler methods that are called knowingly by the user.
        // Such as a button being clicked.
        private void btnRun_Click(object sender, EventArgs e)
        {
            runQuery(false);
        }

        private void btnRunTable_Click(object sender, EventArgs e)
        {
            runQuery(true);
        }

        private void btnApplyChanges_Click(object sender, EventArgs e)
        {
            if (sqlQry is SqlTable)
            {
                grdResults.BindingContext[grdResults.DataSource].EndCurrentEdit();
                doTableChanges();
            }
        }
        private void reQry_KeyDown(object sender, KeyEventArgs e)
        {
            int i = reQry.SelectionStart;
            switch (e.KeyCode)
            {
                case Keys.F5:
                    {
                        runQuery(false);
                        break;
                    }
                case Keys.Space:
                    {
                        if (iCMD != -1)
                        {
                            reQry.Select(i - curWord.Length, curWord.Length);
                            reQry.SelectionColor = SqlConst.GetSqlWordColor(lbxCMD.Items[iCMD].ToString().ToLower());
                            reQry.SelectedText = lbxCMD.Items[iCMD].ToString().ToUpper();
                            reQry.SelectionStart = reQry.Text.Length;
                            reQry.SelectionColor = Color.Black;
                            curWord = "";
                            //e.Handled = true;
                            //return;				  			
                        }
                        if (lbxWords.Items.IndexOf(curWord) < 0)
                            lbxWords.Items.Add(curWord.Trim());
                        curWord = "";
                        break;
                    }
                case Keys.Back:
                    {
                        if (curWord != "")
                            curWord = curWord.Remove(curWord.Length - 1, 1);
                        break;
                    }
                default:
                    {
                        if (e.KeyCode >= Keys.A && e.KeyCode <= Keys.Z)
                            curWord += e.KeyCode.ToString().ToLower();
                        else
                        {
                            if (lbxWords.Items.IndexOf(curWord) < 0)
                                lbxWords.Items.Add(curWord.Trim());
                            curWord = "";
                        }
                        break;
                    }
            }
            //
            lblCurW.Text = curWord;
            iCMD = findReservedWord();
            if (iCMD >= 0)
            {
                lbxCMD.SelectedIndex = iCMD;
                lblCurWord.Text = lbxCMD.Items[iCMD].ToString();
            }
            else
            {
                lblCurWord.Text = "--";
            }
        }
        private void reQryDragEnterEvent(object sender, DragEventArgs e)
        {
            if (e.Data.GetDataPresent(DataFormats.Text))
                e.Effect = DragDropEffects.Copy;
            else
                e.Effect = DragDropEffects.None;
        }

        private void reQryDragDropEvent(object sender, DragEventArgs e)
        {
            if (e.Data.GetData(DataFormats.Text).ToString() == "FieldList")
            {
                string selectedList = "";
                for (int i = 0; i < lbxFieldList.SelectedItems.Count; i++)
                {
                    selectedList += lbxFieldList.SelectedItems[i].ToString() + ", ";
                }
                reQry.Select(reQry.SelectionStart, 0);
                reQry.SelectionColor = Color.Black;
                reQry.SelectedText = selectedList;
                reQry.SelectionStart = reQry.Text.Length;
            }
        }
        private void lbxFieldList_MouseDown(object sender, MouseEventArgs e)
        {
            lbxFieldList.DoDragDrop("FieldList", DragDropEffects.Copy | DragDropEffects.Move);
        }
        private void grdTables_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            reQry.Select(reQry.SelectionStart, 0);
            reQry.SelectionColor = Color.Black;
            reQry.SelectedText = grdTables.CurrentCell.Value.ToString() + " ";
            reQry.SelectionStart = reQry.Text.Length;
        }
        private void grdResults_MouseUp(object sender, MouseEventArgs e)
        {
            if (grdResults.SelectedCells.Count > 1)
            {
                int firstColIndex = grdResults.SelectedCells[0].ColumnIndex;
                bool doSum = (grdResults.Columns[firstColIndex].ValueType != typeof(Char) && grdResults.Columns[firstColIndex].ValueType != typeof(String)
                     && grdResults.Columns[firstColIndex].ValueType != typeof(Boolean) && grdResults.Columns[firstColIndex].ValueType != typeof(DateTime));
                decimal total = 0;
                int count = 0;
                int sumCnt = 0;
                foreach (DataGridViewCell dc in grdResults.SelectedCells)
                {
                    if (doSum && dc.ColumnIndex == firstColIndex)
                    {
                        total += Convert.ToDecimal(dc.Value);
                        sumCnt++;
                    }
                    count++;
                }
                stsCount.Text = "   Cell count = " + count + "     ";
                if (doSum)
                    stsCount.Text += " | Column: " + grdResults.Columns[firstColIndex].HeaderText + " -> Sum = " + total.ToString("N") + " | Avg: " + (total / sumCnt).ToString("N");

            }
            else
            {
                stsCount.Text = "";
            }
        }
        #endregion


    }
}
